﻿using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelTools
{
    public class ReadingDataFromExcel
    {

        /// <summary>
        /// 单sheet页数据读取成DataTable
        /// </summary>
        /// <param name="sheet">sheet页对象</param>
        /// <param name="hearIndex">表头位置</param>
        /// <param name="hasHeadRowFlag">是否读取表头字段作为列名</param>
        /// <param name="colCnt">固定列数</param>
        /// <returns></returns>
        public static DataTable SheetToDataTable(ISheet sheet, int hearIndex = 0, bool hasHeadRowFlag = true, int colCnt = 0)
        {

            DataTable dt = new();

            IRow headerRow = sheet.GetRow(hearIndex);
            if (headerRow.Cells.Count <= 0)
            {
                return null;
            }

            if (hasHeadRowFlag)
            {
                foreach (var item in headerRow.Cells)
                {
                    if (colCnt > dt.Columns.Count)
                    {
                        dt.Columns.Add(item.StringCellValue);
                    }
                }
                while (colCnt > dt.Columns.Count)
                {
                    dt.Columns.Add(new DataColumn());
                }
            }
            else
            {
                var tIndex = 0;
                foreach (var item in headerRow.Cells)
                {
                    tIndex++;
                    dt.Columns.Add(tIndex.ToString());
                }
            }

            int rowIndex = 0;
            foreach (IRow row in sheet)
            {
                if (rowIndex++ < hearIndex + 1) continue;

                DataRow dataRow = dt.NewRow();
                dataRow.ItemArray = row.Cells.FindAll(x => x.ColumnIndex < dt.Columns.Count).Select(c => c.ToString()).ToArray();

                dt.Rows.Add(dataRow);
            }
            return dt;
        }

    }
}
